<?php

namespace app\admin\controller\kuerp;

use app\common\controller\Backend;
use addons\kuerp\model\Business;
use app\admin\model\kuerp\GoodsSales;
use addons\kuerp\model\Staff;
use app\admin\model\kuerp\Customers;
use app\admin\model\kuerp\CustomersUser;
use app\admin\model\kuerp\Collection;
use app\admin\model\kuerp\Record;
use app\admin\model\department\Admin as DepartmentAdmin;
use app\admin\model\kuerp\SaleGoalsDetail;
use app\admin\model\kuerp\StaffMember;
use app\admin\model\kuerp\StaffSignIn;
use app\admin\model\Admin;
use think\Db;

/**
 * 控制台
 * @icon fa fa-dashboard
 * @remark 用于展示当前系统中的统计数据、统计报表及重要实时数据
 */
class Dashboard extends Backend {

    protected $noNeedRight = ['*'];
    protected $adminids = [];
    protected $sale_users = [];

    public function _initialize()
    {
        parent::_initialize();
        //部门经理查看部门成员 上级部门能查看下级部门
        $this->adminids = $this->auth->adminids ?? [0];
        $this->sale_users = $this->auth->childrenSales ?? [0];
    }

    /**
     * 查看
     */
    public function index() {

        $times=input('times','thismonth');
        $type=input('type',2);
        
        if ($type == 1) {//我的
            $whereStaff['staff_id'] = $this->auth->staff_member_id;
            // $whereStaff1['staff_id'] =  ['exp', Db::raw(' ='.$this->auth->staff_member_id.'  OR FIND_IN_SET('.$this->auth->id.',collaborator)')];
            $whereAdmin['admin_id'] = $this->auth->id;
            $active='second';
        } elseif ($type == 2) {//本人及下属
            $whereStaff['staff_id'] = ['in', $this->sale_users];
            // $whereStaff1['staff_id'] = ['exp', Db::raw(' in ('.implode(',', $this->sale_users).')  OR FIND_IN_SET('.$this->auth->id.',collaborator)')];
            $whereAdmin['admin_id'] = ['in', $this->adminids];
            $active='first';
        } elseif ($type == 3) {//仅下属
            $sale_users = $this->sale_users;
            if (in_array($this->auth->staff_member_id, $sale_users)) {
                $sale_users = array_diff($sale_users, [$this->auth->staff_member_id]);
            }
            $adminids = $this->adminids;
            if (in_array($this->auth->id, $adminids)) {
                $adminids = array_diff($adminids, [$this->auth->id]);
            }
            $whereStaff['staff_id'] = ['in', $sale_users];
            // $whereStaff1['staff_id'] = ['exp', Db::raw(' in ('.implode(',', $sale_users).')  OR FIND_IN_SET('.$this->auth->id.',collaborator)')];//['in', $sale_users];
            $whereAdmin['admin_id'] = ['in', $adminids];
            $active='third';
        }else{
            $this->error('参数错误');
        }
        $where1=[
            'createtime'=>['between',setTimes($times,'time')],
            'staff_member_id'=>$whereStaff['staff_id']
        ];
        //新增客户
        $addCustomer= Customers::where($where1)->count();
        //新增联系人
        $where=[
            'customers_user.createtime'=>['between',setTimes($times,'time')],
            'staff_member_id'=>$whereStaff['staff_id']
        ];
        $addContacts= CustomersUser::with(['customers'])->where($where)->count();
        //新增商机
        $addBusiness=0;//Business::where($where1)->count();
        //商机金额
        $businessMoney=0;//Business::where($where1)->sum('money');
        //新建跟进记录
        $addRecord=Record::where(['relation_type' => 1,'follow_type'=>['neq','其它'],'createtime'=>$where1['createtime'],'admin_id'=>$whereAdmin['admin_id']])->count();
        //新建拜访签到
        $addSignIn=StaffSignIn::where(['createtime'=>$where1['createtime'],'staff_id'=>$whereStaff['staff_id']])->count();

        $where2=[
            'purchase_time'=>['between',setTimes($times,'time')],
            'sale_user'=>$whereStaff['staff_id']
        ];
        //新增合同
        $addContract=GoodsSales::where($where2)->count();
        //合同金额
        $contractstatic=GoodsSales::where($where2)->field('sum(real_money+back_money) as total_money, sum(real_money-pay_money) as unback_money,sum(pay_money) as pay_money')->find();
        $contractMoney=$contractstatic['total_money'];//GoodsSales::where($where2)->sum('real_money+back_money');
        $unbackMoney=$contractstatic['unback_money'];

        $where3=[
            'purchase_time'=>['between',setTimes($times,'time')],
            'staff_member_id'=>$whereStaff['staff_id']
        ];
        //回款金额
        $receivablesMoney = $contractstatic['pay_money'];//Collection::where($where3)->where(['examine_status'=>1])->sum('money');
        $briefing=[
            'addCustomer'=>$addCustomer,
            'addContacts'=>$addContacts,
            'addBusiness'=>$addBusiness,
            'addContract'=>$addContract,
            'contractMoney'=>round($contractMoney, 2),
            'unbackMoney'=>round($unbackMoney, 2),
            'businessMoney'=>$businessMoney,
            'receivablesMoney'=>round($receivablesMoney, 2),
            'addRecord'=>$addRecord,
            'addSignIn'=>$addSignIn,
        ];

        if(!in_array($times,['tomorrow','nextweek','nextmonth','nextquarter','nextyear'])){//未来事件不比较数据
            //数据比较
            if(in_array($times,['today','yesterday'])){//日
                if($times == 'today'){
                    $ratio_text='较昨天';
                }else{
                    $ratio_text='较前天';
                }
                $start = $where1['createtime'][1][0];
                $end = $where1['createtime'][1][1];
                $where1['createtime'] = ['between', [strtotime('-1 day', $start), strtotime('-1 day', $end)]];
                $start = $where2['purchase_time'][1][0];
                $end = $where2['purchase_time'][1][1];
                $where2['purchase_time'] = ['between', [strtotime('-1 day', $start), strtotime('-1 day', $end)]];
                $start = $where3['purchase_time'][1][0];
                $end = $where3['purchase_time'][1][1];
                $where3['purchase_time'] = ['between', [strtotime('-1 day', $start), strtotime('-1 day', $end)]];

            }else if(in_array($times,['thisweek','lastweek'])){//周
                if($times == 'thisweek'){
                    $ratio_text='较上周';
                }else{
                    $ratio_text='较上上周';
                }
                $start = $where1['createtime'][1][0];
                $end = $where1['createtime'][1][1];
                $where1['createtime'] = ['between', [strtotime('-1 week', $start), strtotime('-1 week', $end)]];
                $start = $where2['purchase_time'][1][0];
                $end = $where2['purchase_time'][1][1];
                $where2['purchase_time'] = ['between', [strtotime('-1 week', $start), strtotime('-1 week', $end)]];
                $start = $where3['purchase_time'][1][0];
                $end = $where3['purchase_time'][1][1];
                $where3['purchase_time'] = ['between', [strtotime('-1 week', $start), strtotime('-1 week', $end)]];

            }else if(in_array($times,['thismonth','lastmonth',])){//月
                if($times == 'thismonth'){
                    $ratio_text='较上月';
                }else{
                    $ratio_text='较上上月';
                }
                $start = $where1['createtime'][1][0];
                $end = $where1['createtime'][1][1];
                $where1['createtime'] = ['between', [strtotime('-1 month', $start), strtotime('-1 month', $end)]];
                $start = $where2['purchase_time'][1][0];
                $end = $where2['purchase_time'][1][1];
                $where2['purchase_time'] = ['between', [strtotime('-1 month', $start), strtotime('-1 month', $end)]];
                $start = $where3['purchase_time'][1][0];
                $end = $where3['purchase_time'][1][1];
                $where3['purchase_time'] = ['between', [strtotime('-1 month', $start), strtotime('-1 month', $end)]];

            }else if(in_array($times,['thisquarter','lastquarter'])){//季度
                if($times == 'thisquarter'){
                    $ratio_text='较上季度';
                }else{
                    $ratio_text='较上上季度';
                }
                $start = $where1['createtime'][1][0];
                $end = $where1['createtime'][1][1];
                $where1['createtime'] = ['between', [strtotime('-3 month', $start), strtotime('-3 month', $end)]];
                $start = $where2['purchase_time'][1][0];
                $end = $where2['purchase_time'][1][1];
                $where2['purchase_time'] = ['between', [strtotime('-3 month', $start), strtotime('-3 month', $end)]];
                $start = $where3['purchase_time'][1][0];
                $end = $where3['purchase_time'][1][1];
                $where3['purchase_time'] = ['between', [strtotime('-3 month', $start), strtotime('-3 month', $end)]];

            }else if(in_array($times,['thisyear','lastyear'])){//年
                if($times == 'thisyear'){
                    $ratio_text='较去年';
                }else{
                    $ratio_text='较前年';
                }
                $start = $where1['createtime'][1][0];
                $end = $where1['createtime'][1][1];
                $where1['createtime'] = ['between', [strtotime('-1 year', $start), strtotime('-1 year', $end)]];
                $start = $where2['purchase_time'][1][0];
                $end = $where2['purchase_time'][1][1];
                $where2['purchase_time'] = ['between', [strtotime('-1 year', $start), strtotime('-1 year', $end)]];
                $start = $where3['purchase_time'][1][0];
                $end = $where3['purchase_time'][1][1];
                $where3['purchase_time'] = ['between', [strtotime('-1 year', $start), strtotime('-1 year', $end)]];
            }else{
                $ratio_text='';
            }
            //新增客户
            $last_addCustomer=Customers::where($where1)->count();
            //新增联系人
            $where=[
                'customers_user.createtime'=>$where1['createtime'],
                'staff_member_id'=>$whereStaff['staff_id']
            ];
            $last_addContacts= CustomersUser::with(['customers'])->where($where)->count();
            //新增商机
            $last_addBusiness=0;//Business::where($where1)->count();
            //商机金额
            $last_businessMoney=0;//Business::where($where1)->sum('money');
            //新建跟进记录
            $last_addRecord=Record::where(['relation_type' => 1,'follow_type'=>['neq','其它'],'createtime'=>$where1['createtime'],'admin_id'=>$whereAdmin['admin_id']])->count();
            //新建拜访签到
            $last_addSignIn=StaffSignIn::where(['createtime'=>$where1['createtime'],'staff_id'=>$whereStaff['staff_id']])->count();
            //新增合同
            $last_addContract=GoodsSales::where($where2)->count();
            //合同金额
            $last_contractStatic=GoodsSales::where($where2)->field('sum(real_money+back_money) as total_money, sum(real_money-pay_money) as unback_money,sum(pay_money) as pay_money')->find();
            $last_contractMoney=$last_contractStatic['total_money'];//GoodsSales::where($where2)->sum('real_money+back_money');
            $last_unbackMoney = $last_contractStatic['unback_money'];
            //回款金额
            $last_receivablesMoney = $last_contractStatic['pay_money'];//Collection::where($where3)->where(['examine_status'=>1])->sum('money');
            $briefing['ratio_text']=$ratio_text;
            $briefing['customer_ratio'] = $this->getRatio($addCustomer, $last_addCustomer);
            $briefing['contacts_ratio'] = $this->getRatio($addContacts, $last_addContacts);
            $briefing['business_ratio'] = $this->getRatio($addBusiness, $last_addBusiness);
            $briefing['business_money_ratio'] = $this->getRatio($businessMoney, $last_businessMoney);
            $briefing['record_ratio'] = $this->getRatio($addRecord, $last_addRecord);
            $briefing['signin_ratio'] = $this->getRatio($addSignIn, $last_addSignIn);
            $briefing['contract_ratio'] = $this->getRatio($addContract, $last_addContract);
            $briefing['contract_money_ratio'] = $this->getRatio($contractMoney, $last_contractMoney);
            $briefing['receivables_money_ratio'] = $this->getRatio($receivablesMoney, $last_receivablesMoney);
            $briefing['unback_money_ratio'] = $this->getRatio($unbackMoney, $last_unbackMoney);
        }


        //6个月内的曲线
        $month6 = date('Y-m-d', strtotime('-6 month')). ',' . date('Y-m-d') ;
        $where4 = [
            'createtime' => ['between', setTimes($month6, 'time')],
            'staff_member_id'=>$whereStaff['staff_id']
        ];
        $customer6=Customers::where($where4)
            ->field("FROM_UNIXTIME(createtime,'%Y-%m') as ctime,count(*) as c")->group('ctime')->select();
        $where = [
            'customers_user.createtime' => ['between', setTimes($month6, 'time')],
            'staff_member_id'=>$whereStaff['staff_id']
        ];
        $contacts6=CustomersUser::with(['customers'])->where($where)
            ->field("FROM_UNIXTIME(customers_user.createtime,'%Y-%m') as ctime,count(*) as c")->group('ctime')->select();

        $business6=[];//Business::where($where4)
            // ->field("FROM_UNIXTIME(createtime,'%Y-%m') as ctime,count(*) as c")->group('ctime')->select();

        $businessmoney6=[];//Business::where($where4)
            // ->field("FROM_UNIXTIME(createtime,'%Y-%m') as ctime,sum(money) as c")->group('ctime')->select();

        $record6=Record::where(['relation_type' => 1,'follow_type'=>['neq','其它'],'createtime'=>$where1['createtime'],'admin_id'=>$whereAdmin['admin_id']])
            ->field("FROM_UNIXTIME(createtime,'%Y-%m') as ctime,count(*) as c")->group('ctime')->select();
        $signin6=StaffSignIn::where(['createtime'=>$where1['createtime'],'staff_id'=>$whereStaff['staff_id']])
            ->field("FROM_UNIXTIME(createtime,'%Y-%m') as ctime,count(*) as c")->group('ctime')->select();
        $where5 = [
            'purchase_time' => ['between', setTimes($month6, 'time')],
            'sale_user'=>$whereStaff['staff_id']
        ];

        //新增合同
        $contract6 = GoodsSales::where($where5)->field("FROM_UNIXTIME(purchase_time,'%Y-%m') as ctime,count(*) as c")->group('ctime')->select();;
        //合同金额
        $contractMoney6 = GoodsSales::where($where5)->field("FROM_UNIXTIME(purchase_time,'%Y-%m') as ctime,sum(real_money+back_money) as c,sum(real_money-pay_money) as unback_money,sum(pay_money) as pay_money")->group('ctime')->select();
        $unbackMoney6 = [];
        foreach ($contractMoney6 as $k=>$v){
            $unbackMoney6[]=[
                'ctime'=>$v['ctime'],
                'c'=>$v['unback_money'],
            ];
        }
        $where6 = [
            'purchase_time' => ['between', setTimes($month6, 'time')],
            'staff_member_id'=>$whereStaff['staff_id']
        ];
        //回款金额
        $receivablesMoney=[];//Collection::where($where6)->where(['examine_status'=>1])->field("FROM_UNIXTIME(purchase_time,'%Y-%m') as ctime,sum(money) as c")->group('ctime')->select();
        foreach ($contractMoney6 as $k=>$v){
            $receivablesMoney[]=[
                'ctime'=>$v['ctime'],
                'c'=>$v['pay_money'],
            ];
        }
        $this->view->assign([
            'briefing' => $briefing,
            'type' => $type,
            'times' => $times,
            'active' => $active,
            'dates' => input('dates'),
            'wheredate' =>implode(' - ',setTimes($times,'date')),
            'wheredatetime' => implode(' - ',setTimes($times,'datetime')),

        ]);
        $d=setTimes($month6, 'date');
        $echarts=[
            'customer'=>$this->getValues($d,$customer6),
            'contacts'=>$this->getValues($d,$contacts6),
            'contract'=>$this->getValues($d,$contract6),
            'business'=>$this->getValues($d,$business6),
            'business_money'=>$this->getValues($d,$businessmoney6),
            'record'=>$this->getValues($d,$record6),
            'signin'=>$this->getValues($d,$signin6),
            'contract_money'=>$this->getValues($d,$contractMoney6),
            'receivables_money'=>$this->getValues($d,$receivablesMoney),
            'unback_money'=>$this->getValues($d,$unbackMoney6),
        ];

        // $year1=strtotime('-1 year');
        // $month6=strtotime('-6 month');
        // $month3=strtotime('-3 month');
        // $day30=strtotime('-30 day');
        // $day15=strtotime('-15 day');
        // $day7=strtotime('-7 day');
        // $where7day=[
        //     'last_follow_time'=>['between',[$day15,$day7]],
        //     'staff_member_id'=>$whereStaff['staff_id']
        // ];
        // $where15day=[
        //     'last_follow_time'=>['between',[$day30,$day15]],
        //     'staff_member_id'=>$whereStaff['staff_id']
        // ];
        // $where30day=[
        //     'last_follow_time'=>['between',[$month3,$day30]],
        //     'staff_member_id'=>$whereStaff['staff_id']
        // ];
        // $where3month=[
        //     'last_follow_time'=>['between',[$month6,$month3]],
        //     'staff_member_id'=>$whereStaff['staff_id']
        // ];
        // $where6month=[
        //     'last_follow_time'=>['between',[$year1,$month6]],
        //     'staff_member_id'=>$whereStaff['staff_id']
        // ];
        // $over=[
        //     'next_follow_time'=>['between',[$year1,time()]],
        //     'staff_member_id'=>$whereStaff['staff_id']
        // ];
        // $day7count = Customers::where($where7day)->count();
        // $day15count = Customers::where($where15day)->count();
        // $day30count = Customers::where($where30day)->count();
        // $month3count = Customers::where($where3month)->count();
        // $month6count = Customers::where($where6month)->count();
        // $overcount = Customers::where($over)->count();
        // $this->view->assign('nocontacts', [
        //     'day7count' => $day7count,
        //     'day15count' => $day15count,
        //     'day30count' => $day30count,
        //     'month3count' => $month3count,
        //     'month6count' => $month6count,
        //     'overcount' => $overcount,
        //     'where7day' => implode(' - ', $where7day['last_follow_time'][1]),
        //     'where15day' => implode(' - ', $where15day['last_follow_time'][1]),
        //     'where30day' => implode(' - ', $where30day['last_follow_time'][1]),
        //     'where3month' => implode(' - ', $where3month['last_follow_time'][1]),
        //     'where6month' => implode(' - ', $where6month['last_follow_time'][1]),
        //     'over' => implode(' - ', $over['next_follow_time'][1]),
        // ]);
        // $this->view->assign('summary',$this->getCustomer());

        $this->assignconfig('echarts',$echarts);
        $menuInfo = get_menu_title();
        $title = $menuInfo['title'];
        $this->view->assign('title', $title);
        return $this->view->fetch();
    }
    /**
     * 业绩指标完成率
     */
    public function get_achievement_detail()
    {
        $times = input('times','thismonth');
        $status = input('status', 1);//1订单 2回款
        $type = input('type', 3);//1仅本人 2团队 3仅下属
        $admins = $this->auth->id;
        if ($type == 1) {//我的
            $staff_id = $this->auth->staff_member_id;
        } elseif ($type == 2) {//本人及下属
            $staff_id = ['in', $this->sale_users];
            $admins = ['in', $this->adminids];
        } elseif ($type == 3) {//仅下属
            $sale_users = $this->sale_users;
            if (in_array($this->auth->staff_member_id, $sale_users)) {
                $sale_users = array_diff($sale_users, [$this->auth->staff_member_id]);
            }
            $staff_id = ['in', $sale_users];
            $admins = ['in', array_diff($this->adminids,$this->auth->id)];
        }else{
            $this->error('参数错误');
        }
        $times=setTimes($times,'time');
        $years=getYearByTime($times[0], $times[1]);
        $model = new SaleGoalsDetail();
        $achievement = $model->where([
            // 'admin_id' => $this->auth->id,
            'admin_id' => $admins,
            'type' => 2,
            'year' => ['in',$years],
            'model_class' => $status
        ])->select();

        $achievementMoney = 0.00;
        //获取需要查询的月份
        $month = getmonthByTime($times[0], $times[1]);
        foreach ($achievement as $k=>$v) {
            foreach ($month as $key=>$val) {
                if ($v['year'] == $key) {
                    foreach ($val as $key1=>$val1) {
                        $achievementMoney += $v['month_'.(int)$val1];
                    }
                }
            }
        }
        // $where['order_date'] = ['between', $times];
        // $where['check_status'] = 2;
        $where['sale_user'] = $staff_id;
        $contractStatic = GoodsSales::where([
            'purchase_time'=>['between', $times],
            // 'check_status'=>2,
            'sale_user'=>$staff_id,
        ])->field('sum(real_money+back_money) as contractMoney, sum(pay_money) as payMoney')->find();
        $contractMoney = $contractStatic['contractMoney'] ? : '0.00';
        $receivablesMoney = $contractStatic['payMoney'] ? : '0.00';
        // $contractMoney = GoodsSales::where([
        //     'purchase_time'=>['between', $times],
        //     // 'check_status'=>2,
        //     'sale_user'=>$staff_id,
        // ])->sum('real_money+back_money');

        // $receivablesMoney = Collection::where([
        //     'purchase_time'=>['between', $times],
        //     'examine_status'=>1,
        //     'staff_member_id'=>$staff_id,
        // ])->sum('money');

        $data = [];
        $data['contractMoney'] = round($contractMoney ? : 0, 2);
        $data['receivablesMoney'] = round($receivablesMoney ? : 0, 2);
        $data['achievementMoney'] = round($achievementMoney ? : 0, 2);
        //完成率
        $rate = 0.00;
        if ($status == 1) {
            $rate = $achievementMoney ? round(($contractMoney/$achievementMoney),4) : 0.00;
        } else {
            $rate = $achievementMoney ? round(($receivablesMoney/$achievementMoney),4) : 0.00;
        }
        $data['rate'] = round($rate *100,2);
        $this->success('请求成功','', $data);
    }

    /**
     * 合同金额目标及完成情况
     */
    public function get_achievement()
    {
        $times = input('times','thismonth');
        $status = input('status', 1);//1合同 2回款
        $type = input('type', 2);//1仅本人 2团队 3仅下属
        $admins = $this->auth->id;
        if ($type == 1) {//我的
            $staff_id = $this->auth->staff_member_id;
        } elseif ($type == 2) {//本人及下属
            $staff_id = ['in', $this->sale_users];
            $admins = ['in', $this->adminids];
        } elseif ($type == 3) {//仅下属
            $sale_users = $this->sale_users;
            if (in_array($this->auth->staff_member_id, $sale_users)) {
                $sale_users = array_diff($sale_users, [$this->auth->staff_member_id]);
            }
            $staff_id = ['in', $sale_users];
            $admins = ['in', array_diff($this->adminids,$this->auth->id)];
        }else{
            $this->error('参数错误');
        }
        $dates=setTimes($times,'time');
        $years=getYearByTime($dates[0], $dates[1]);

        $model = new SaleGoalsDetail();
        $fieldmonth = ['year'];
        for($i=1;$i<=12;$i++) {
            $fieldmonth[] = 'sum(month_'.$i.') as month_'.$i;
        }
        $achievement = $model->where([
            'admin_id' => $admins,
            'type' => 2,
            'year' => ['in',$years],
            'model_class' => $status
        ])->field($fieldmonth)->group('year')->select();
        $achievement=collection($achievement)->toArray();
        if (in_array($times, ['thisquarter', 'lastquarter', 'nextquarter'])) {//季度
            if($status == 1){
                $where['purchase_time'] = ['between', [strtotime($years[0] . '-01-01'), strtotime(($years[0] + 1). '-12-31')]];
                // $where['check_status'] = 2;
                $where['sale_user'] = $staff_id;
                $moneys = GoodsSales::where($where)->group('month')
                    ->field('sum(real_money+back_money) as number,FROM_UNIXTIME(purchase_time,"%Y-%m") as month')
                    ->select();
            }else{
                $where2['purchase_time'] = ['between', [strtotime($years[0] . '-01-01'), strtotime(($years[0] + 1). '-12-31')]];
                $where2['sale_user'] = $staff_id;
                $moneys = GoodsSales::where($where2)->group('month')
                    ->field('sum(pay_money) as number,FROM_UNIXTIME(purchase_time,"%Y-%m") as month')
                    ->select();
                // $where2['examine_status'] = 1;
                // $where2['staff_member_id'] = $staff_id;
                // $moneys = Collection::where($where2)->group('month')
                //     ->field('sum(money) as number,FROM_UNIXTIME(purchase_time,"%Y-%m") as month')
                //     ->select();
            }
            $list=[];
            foreach ($moneys as $v){
                $list[$v['month']]= round($v['number'],2);
            }
            $achievementMoney=[];
            foreach ($achievement as $k=>$v) {
                foreach ($v as $field => $val) {
                    $achievementMoney[$v['year'] .'-'. SaleGoalsDetail::getFieldMonth($field)]=$val;
                }
            }
            $result=['times'=>[],'achievement'=>[],'money'=>[]];
            $startTime=strtotime($years[0] . '-01-01');
            // for($i=1;$i<=8;$i++){
            //     $m=$i*2;
            //     $result['times'][]=[
            //         date('Y-m',strtotime("+".($m-2)." month",$startTime)),
            //         date('Y-m',strtotime("+".($m-1)." month",$startTime)),
            //         date('Y-m',strtotime("+".$m." month",$startTime)),
            //     ];
            // }
            for($i = 0; $i < 4; $i++) {
                $quarterStart = $i * 3;
                $result['times'][] = [
                    date('Y-m', strtotime("+" . $quarterStart . " month", $startTime)),
                    date('Y-m', strtotime("+" . ($quarterStart + 1) . " month", $startTime)), 
                    date('Y-m', strtotime("+" . ($quarterStart + 2) . " month", $startTime))
                ];
            }
            foreach ($result['times'] as $k=>$t){
                $a=0;$l=0;
                foreach ($t as $t1) {
                    $a += $achievementMoney[$t1] ?? 0;
                    $l += $list[$t1] ?? 0;
                }
                $result['times'][$k]=implode(',',$t);
                $result['achievement'][]=$a;
                $result['money'][]=$l;
            }

        } else if (in_array($times, ['thisyear', 'lastyear', 'nextyear'])) {//年
            if($status == 1){
                $where['purchase_time'] = ['between', [strtotime(($years[0] - 2) . '-01-01'), strtotime(($years[0] + 2) . '-12-31')]];
                // $where['check_status'] = 2;
                $where['sale_user'] = $staff_id;
                $moneys = GoodsSales::where($where)->group('month')
                    ->field('sum(real_money+back_money) as number,FROM_UNIXTIME(purchase_time,"%Y") as month')
                    ->select();
            }else{
                $where2['purchase_time'] = ['between', [strtotime(($years[0] - 2) . '-01-01'), strtotime(($years[0] + 2) . '-12-31')]];
                $where2['sale_user'] = $staff_id;
                $moneys = GoodsSales::where($where2)->group('month')
                    ->field('sum(pay_money) as number,FROM_UNIXTIME(purchase_time,"%Y-%m") as month')
                    ->select();
                // $where2['examine_status'] = 2;
                // $where2['staff_member_id'] = $staff_id;
                // $moneys = Collection::where($where2)->group('month')
                //     ->field('sum(money) as number,FROM_UNIXTIME(purchase_time,"%Y") as month')
                //     ->select();
            }
            $list=[];
            foreach ($moneys as $v){
                $list[$v['month']]= round($v['number'],2);
            }
            $achievementMoney=[];
            foreach ($achievement as $k=>$v) {
                foreach ($v as $field => $val) {
                    $achievementMoney[$v['year']]=$val;
                }
            }
            $result=['times'=>[],'achievement'=>[],'money'=>[]];
            $startTime=strtotime(($years[0]-2) . '-01-01');
            for ($i = 0; $i < 5; $i++) {
                $result['times'][] = date('Y', strtotime("+{$i} year", $startTime));
            }
            foreach ($result['times'] as $t){
                $result['achievement'][]=$achievementMoney[$t]??0;
                $result['money'][]=$list[$t]??0;
            }
        }else{
                if($status == 1){
                    $where['purchase_time'] = ['between', [strtotime($years[0] . '-01-01'), strtotime($years[0] . '-12-31')]];
                    // $where['check_status'] = 2;
                    $where['sale_user'] = $staff_id;
                    $moneys = GoodsSales::where($where)->group('month')
                        ->field('sum(real_money+back_money) as number,FROM_UNIXTIME(purchase_time,"%Y-%m") as month')
                        ->select();
                }else{
                    $where2['purchase_time'] = ['between', [strtotime($years[0] . '-01-01'), strtotime($years[0] . '-12-31')]];
                    $where2['sale_user'] = $staff_id;
                    $moneys = GoodsSales::where($where2)->group('month')
                        ->field('sum(pay_money) as number,FROM_UNIXTIME(purchase_time,"%Y-%m") as month')
                        ->select();
                    // $where2['examine_status'] = 1;
                    // $where2['staff_member_id'] = $staff_id;
                    // $moneys = Collection::where($where2)->group('month')
                    //     ->field('sum(money) as number,FROM_UNIXTIME(purchase_time,"%Y-%m") as month')
                    //     ->select();
                }
                $list=[];
                foreach ($moneys as $v){
                    $list[$v['month']]= round($v['number'],2);
                }
                $achievementMoney=[];
                foreach ($achievement as $k=>$v) {
                    foreach ($v as $field => $val) {
                        $achievementMoney[$v['year'] .'-'. SaleGoalsDetail::getFieldMonth($field)]= (float)$val;
                    }
                }
                $result=['times'=>[],'achievement'=>[],'money'=>[]];
                $startTime=strtotime($years[0] . '-01-01');
                for($i=0;$i<12;$i++){
                    $result['times'][]=date('Y-m',strtotime("+{$i} month",$startTime));
                }
                foreach ($result['times'] as $t){
                    $result['achievement'][]=$achievementMoney[$t]??0;
                    $result['money'][]=$list[$t]??0;
                }
        }



        $this->success('请求成功','', $result);
    }

    /**
     * 获取商机
     */
    public function get_business(){
        $times=input('times','thismonth');
        $type=input('type',2);
        if ($type == 1) {//我的
            $whereStaff['staff_id'] = $this->auth->id;
        } elseif ($type == 2) {//本人及下属
            $whereStaff['staff_id'] = ['in', Staff::getMyStaffIds()];
        } elseif ($type == 3) {//仅下属
            $whereStaff['staff_id'] = ['in', Staff::getLowerStaffId()];
        }else{
            $this->error('参数错误');
        }
        $where1=[
            'createtime'=>['between',setTimes($times,'time')],
            'owner_staff_id'=>$whereStaff['staff_id']
        ];
        $contractBusiness=Business::where($where1)->where(['status'=>'4'])->count();
        $contractBusinessMoney=Business::where($where1)->where(['status'=>'4'])->sum('money');
        $noBusiness=Business::where($where1)->where(['status'=>'5'])->count();
        $noBusinessMoney=Business::where($where1)->where(['status'=>'5'])->sum('money');
        $this->success('请求成功','',[
            'contractBusiness'=>$contractBusiness,
            'contractBusinessMoney'=>$contractBusinessMoney,
            'noBusiness'=>$noBusiness,
            'noBusinessMoney'=>$noBusinessMoney,
        ]);
    }

    /**
     * 排行
     */
    public function ranking()
    {
        //1 获客 2拜访 3业绩 4回款 5工单
        $ranking = input('ranking',1);
        $type = input('type',1);//1个人 2部门 
        $times = input('times');//2021-01,2021-07
        if (empty($times)) {
            $this->error('筛选时间不能为空');
        }
        $times = setTimes($times, 'time');
        $startTime = $times[0];
        $endTime = $times[1];
        $where = [];
        $departments = [];
        if ($ranking == 1) {//新增客户数
            $rankingList = Customers::where(['createtime' => ['between', [$startTime, $endTime]]])->where('staff_member_id', '>', 0)->where($where)
                ->with(['staff'])
                ->field('count(*) as number,staff_member_id')->order('number desc')->group('staff_member_id')->select();
            $rankingList = collection($rankingList)->toArray();
           
        } elseif ($ranking == 2) {//跟进记录数
            $rankingList = Record::where(['relation_type' => 1,'follow_type'=>['neq','其它'], 'createtime' => ['between', [$startTime, $endTime]]])
                ->with(['staff'])->field('count(*) as number,admin_id')->order('number desc')->group('admin_id')->select();
            $rankingList = collection($rankingList)->toArray();
        } elseif ($ranking == 3) {//拜访记录数
            $rankingList = StaffSignIn::where([ 'createtime' => ['between', [$startTime, $endTime]]])->where($where)
            ->with(['staff'])->field('count(*) as number,staff_id')->order('number desc')->group('staff_id')->select();
            $rankingList = collection($rankingList)->toArray();
        } elseif ($ranking == 4) {//合同金额
            $rankingList = GoodsSales::where([ 'purchase_time' => ['between', [$startTime, $endTime]]])
                ->where($where)->with(['staff'])->field('sum(real_money+back_money) as number,sale_user')->order('number desc')->group('sale_user')->select();
            $rankingList = collection($rankingList)->toArray();
            // $years=getYearByTime($times[0],$times[1]);
            // $model = new SaleGoalsDetail();
            // $achievement = $model->where([
            //     'obj_id' => $this->auth->id,
            //     'type' => 3,
            //     'year' => ['in',$years],
            //     'status' => 1
            // ])->select();

            // $achievementMoney = 0.00;
            // //获取需要查询的月份
            // $month = getmonthByTime($times[0],$times[1]);
            // foreach ($achievement as $k=>$v) {
            //     foreach ($month as $key=>$val) {
            //         if ($v['year'] == $key) {
            //             foreach ($val as $key1=>$val1) {
            //                 $achievementMoney += $v[Achievement::getMonthField($v['year'].'-'.$val1)];
            //             }
            //         }
            //     }
            // }
            // foreach ($rankingList as $k => &$v) {
            //     $v['ranking'] = $k + 1;
            //     $v['rate'] = $achievementMoney ? round(($v['number']/$achievementMoney),4) : 0.00;
            // }
        } elseif ($ranking == 5) {//合同数
            $rankingList = GoodsSales::where(['purchase_time' => ['between', [$startTime, $endTime]]])
                ->where($where)->with(['staff'])->field('count(*) as number,sale_user')->order('number desc')->group('sale_user')->select();
            $rankingList = collection($rankingList)->toArray();
        } elseif ($ranking == 6) {//回款金额
            $rankingList = Collection::where(['examine_status' => 1, 'purchase_time' => ['between', [$startTime, $endTime]]])->where($where)
                ->with(['staff'])->field('sum(money) as number,staff_member_id')->order('number desc')->group('staff_member_id')->select();
            $rankingList = collection($rankingList)->toArray();
            // $years=getYearByTime($times[0],$times[1]);
            // $model = new Achievement();
            // $achievement = $model->where([
            //     'obj_id' => $this->auth->id,
            //     'type' => 3,
            //     'year' => ['in',$years],
            //     'status' => 2
            // ])->select();

            // $achievementMoney = 0.00;
            // //获取需要查询的月份
            // $month = getmonthByTime($times[0],$times[1]);
            // foreach ($achievement as $k=>$v) {
            //     foreach ($month as $key=>$val) {
            //         if ($v['year'] == $key) {
            //             foreach ($val as $key1=>$val1) {
            //                 $achievementMoney += $v[Achievement::getMonthField($v['year'].'-'.$val1)];
            //             }
            //         }
            //     }
            // }
            // foreach ($rankingList as $k => &$v) {
            //     $v['ranking'] = $k + 1;
            //     $v['rate'] = $achievementMoney ? round(($v['number']/$achievementMoney),4) : 0.00;
            // }
        }
        if ($type == 2) {
            //获取部门信息
            $departments = Db::name('department')->where(['status' => 'normal', 'tags' => '销售'])->field('id,name')->select();
            $departments = collection($departments)->toArray();
            //获取部门下员工信息
            $departmentIds = DepartmentAdmin::getDepartmentGroupAdminIds(array_column($departments, 'id'));
            foreach ($departments as $k => $v) {
                $v['admin_ids'] = $departmentIds[$v['id']] ?? '';
                $departments[$k]['number'] = 0;
                if (!empty($v['admin_ids'])) {
                    $depaadminids = explode(',', $v['admin_ids']);
                    $deprank = array_filter($rankingList, function ($item) use ($depaadminids, $ranking) {
                        return in_array($item['staff'][$ranking != 2 ? 'admin_id' : 'id'], $depaadminids);
                    });
                    $departments[$k]['number'] = round(array_sum(array_column($deprank, 'number')), 2);
                }
            }
            //departments按number排序 倒序
            usort($departments, function ($a, $b) {
                return $b['number'] - $a['number'];
            });
            foreach ($departments as $k => &$v) {
                $v['ranking'] = $k + 1;
            }
            $rankingList = $departments;
        } else {
            if ($ranking != 2) {
                $admin_ids = array_column(array_column($rankingList, 'staff'), 'admin_id');
                $adminList = Admin::where(['id' => ['in', $admin_ids]])->column('id,nickname as name,avatar', 'id');
            }
            foreach ($rankingList as $k => &$v) {
                $v['ranking'] = $k + 1;
                if ($ranking != 2) {
                    $v['staff'] = $adminList[$v['staff']['admin_id']] ?? $v['staff'];
                }
                $v['staff']['avatar'] = cdnurl($v['staff']['avatar'], true);
            }
        }
        $result = array("total" => count($rankingList) ,"data" => $rankingList);

        return json($result);
    }

    /**
     * 获取数据汇总
     */
    private function getCustomer(){
        $times=input('times','thismonth');
        $type=input('type',2);
        if ($type == 1) {//我的
            $whereStaff['staff_id'] = $this->auth->staff_member_id;
            $whereAdmin['admin_id'] = $this->auth->id;
        } elseif ($type == 2) {//本人及下属
            $whereStaff['staff_id'] = ['in', $this->sale_users];
            $whereAdmin['admin_id'] = ['in', $this->adminids];
        } elseif ($type == 3) {//仅下属
            $sale_users = $this->sale_users;
            if (in_array($this->auth->staff_member_id, $sale_users)) {
                $sale_users = array_diff($sale_users, [$this->auth->staff_member_id]);
            }
            $adminids = $this->adminids;
            if (in_array($this->auth->id, $adminids)) {
                $adminids = array_diff($adminids, [$this->auth->id]);
            }
            $whereStaff['staff_id'] = ['in', $sale_users];
            $whereStaff['admin_id'] = ['in', $adminids];
        }else{
            $this->error('参数错误');
        }
        $where1=[
            'createtime'=>['between',setTimes($times,'time')],
            'staff_member_id'=>$whereStaff['staff_id']
        ];

        //新增客户 转成交客户 放入公海客户 公海池领取
        $addCustomer=Customers::where($where1)->count();
        $contractCustomer=0;//Customers::where($where1)->where(['contract_status'=>1])->count();
        $seasCustomer=0;//Customer::where(['createtime'=>$where1['createtime'],
            // 'create_staff_id'=>$where1['owner_staff_id']])->where(['is_seas'=>1])->count();
        $seas=0;//Customer::where(['receivetime'=>$where1['createtime'],'owner_staff_id'=>$where1['owner_staff_id']])->count();
        //新增商机 赢单商机 输单商机 商机总金额
        $addBusiness=0;//Business::where($where1)->count();
        $contractBusiness=0;//Business::where($where1)->where(['status'=>'4'])->count();
        $noBusiness=0;//Business::where($where1)->where(['status'=>'5'])->count();
        $businessMoney=0;//Business::where($where1)->sum('money');
        //签约合同 即将到期警告 已到期 合同金额
        $addContract=GoodsSales::where(['sale_user' => $where1['staff_member_id'],
            'createtime' => ['between', setTimes($times, 'time')]])->count();
        $where7day=[date('Y-m-d'), date('Y-m-d', strtotime('+7 day'))];
        $contract = 0;//GoodsSales::where(['owner_staff_id' => $where1['owner_staff_id']])
            // ->where(['end_time' => ['between',$where7day ]])
            // ->where(['check_status' => 2])->count();
        $wheregtday=[date('Y-m-d', strtotime('-1 year')),date('Y-m-d')];
        $contractExpire = 0;//Contract::where(['owner_staff_id' => $where1['owner_staff_id']])
            // ->where(['end_time' => ['between', $wheregtday], 'contract_status' => 0])
            // ->where(['check_status' => 2])->count();
        $contractMoney=GoodsSales::where(['createtime' => $where1['createtime'], 'sale_user' => $where1['staff_member_id']])->sum('real_money+back_money');
        //跟进客户数 新增待跟进客户数
        $record=Customers::where(['staff_member_id'=>$where1['staff_member_id'],
            'last_follow_time'=>['between',setTimes($times,'time')]])->count();
        $norecord=Customers::where(['staff_member_id'=>$where1['staff_member_id'],
            'last_follow_time'=>['between',setTimes($times,'time')]])->count();
        //回款 预计回款
        $receivables=Collection::where(['purchase_time'=>['between',setTimes($times,'time')],'examine_status'=>1])
            ->where(['staff_member_id'=>$where1['staff_member_id']])->sum('money');
        $receivablesPlan=0;//ReceivablesPlan::where(['return_date'=>['between',setTimes($times,'date')],
            // 'owner_staff_id'=>$where1['owner_staff_id']])->sum('money');
        return [
            'addCustomer'=>$addCustomer,
            'contractCustomer'=>$contractCustomer,
            'seasCustomer'=>$seasCustomer,
            'seas'=>$seas,
            'addBusiness'=> $addBusiness,
            'contractBusiness'=> $contractBusiness,
            'noBusiness'=> $noBusiness,
            'businessMoney'=> $businessMoney,
            'addContract'=>$addContract,
            'contract'=> $contract,
            'contractExpire'=> $contractExpire,
            'contractMoney'=>$contractMoney,
            'record'=> $record,
            'norecord'=> $norecord,
            'receivables'=> $receivables,
            'receivablesPlan'=> $receivablesPlan,
            'where7day'=> implode(' - ',$where7day),
            'wheregtday'=> implode(' - ',$wheregtday),
        ];
    }

    /**
     * 获取值
     * @param $times
     * @param $data
     * @return array
     */
    private function getValues($times, $data)
    {
        $result = [];
        $list = [];
        foreach ($data as $v) {
            $list[$v['ctime']] = $v['c'];
        }
        $startTime = strtotime($times[1]);
        for ($i = 5; $i >= 0; $i--) {
            $date = date('Y-m', strtotime("-{$i} month",$startTime));
            $result[] = $list[$date] ?? 0;
        }
        return $result;
    }
    private function getRatio($number,$last_number){

        $last_number = $last_number ?? 0;
        if(empty($number) && empty($last_number)){
            $ratio=0;
        }elseif(empty($number) && $last_number){
            $ratio=-100;
        }elseif(empty($last_number) && $number){
            $ratio=100;
        }else{
            if($last_number > $number){
                $ratio=-(sprintf("%.2f", ($last_number-$number)/$last_number * 100));
            }else{
                $ratio=(sprintf("%.2f", ($number-$last_number)/$last_number * 100));
            }
        }
        return $ratio;
    }

}
